package www.lagou.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import www.lagou.entity.Dept;
import www.lagou.entity.Employee;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.List;

public class EmployeeDao {

    /**
     * 需求1: 查询所有的员工信息 (不包含没有部门的员工)。
     * @return
     * @throws SQLException
     */
    public List<Employee> findEmployeeByNull() throws SQLException {

        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

        //2.编写SQL
        String sql = "SELECT * FROM employee a,dept b WHERE a.did IS NOT NULL AND a.`did`= b.`id`";

        List<Employee> list = qr.query(sql,new BeanListHandler<Employee>(Employee.class));

        for(Employee employee:list){
            Dept dept = findDeptById(employee.getDid());
            employee.setDept(dept);
        }

        return list;
    }

    public List<Employee> findEmployee() throws SQLException {

        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

        //2.编写SQL
        String sql = "SELECT * FROM employee";

        List<Employee> list = qr.query(sql,new BeanListHandler<Employee>(Employee.class));

        for(Employee employee:list){
            Dept dept = findDeptById(employee.getDid());
            employee.setDept(dept);
        }

        return list;
    }

    public Dept findDeptById(int did) throws SQLException {

        //1.创建QueryRunner对象 自动模式,传入数据库连接池
        QueryRunner qr = new QueryRunner(DruidUtils.dataSource);

        //2.编写SQL
        String sql = "SELECT * FROM dept WHERE id = ?";

        Dept dept = qr.query(sql,new BeanHandler<Dept>(Dept.class),did);

        return dept;
    }
}
